﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Controller
{
    /// <summary>
    /// Summary description for Activity Controller
    /// </summary>
    public class NPSMarketingTypeController : ControllerBase
    {
        public int InsertNPSMarketingTypeData(Entities.NPSMarketingType ciMarketingType)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingType.sxTitle;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingType.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_I_P_NPS_MARKETING_TYPE";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            return Convert.ToInt32(lsParams[0].Value);
        }

        public void UpdateNPSMarketingTypeData(Entities.NPSMarketingType ciMarketingType)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciMarketingType.iID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingType.sxTitle;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingType.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciMarketingType.sxUpdater;

            String sxQuery = "PR_U_P_NPS_MARKETING_TYPE";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public void DeleteNPSMarketingTypeData(Entities.NPSMarketingType ciMarketingType)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciMarketingType.iID;

            String sxQuery = "PR_D_P_NPS_MARKETING_TYPE";
            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public List<Entities.NPSMarketingType> NPSMarketingTypeList
        {
            get
            {
                DBLib dbl = new DBLib();

                String sxQuery =
                    @"select 
                    P_NPS_MARKETING_TYPE_ID,
	                TITLE,
	                DETAIL,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE,
	                VALID_FROM,
	                VALID_TO
                from 
                    P_NPS_MARKETING_TYPE
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE
                ";
                DataTable dt = dbl.GetDataTable(sxQuery);

                List<Entities.NPSMarketingType> MarketingTypeList = new List<Entities.NPSMarketingType>();
                foreach (DataRow row in dt.Rows)
                {
                    Entities.NPSMarketingType MarketingType = new Entities.NPSMarketingType();
                    MarketingType.iID = Convert.ToInt16(row["P_NPS_MARKETING_TYPE_ID"].ToString());
                    MarketingType.sxTitle = row["TITLE"].ToString();
                    MarketingType.sxDetail = row["DETAIL"].ToString();
                    MarketingType.sxCreator = row["CREATE_BY"].ToString();
                    MarketingType.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                    MarketingType.sxUpdater = row["UPDATE_BY"].ToString();
                    MarketingType.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString()); 
                    MarketingType.dtmValidFrom = DateTime.Parse(row["VALID_FROM"].ToString());
                    MarketingTypeList.Add(MarketingType);
                }
                return MarketingTypeList;
            }
        }

        public List<Entities.NPSMarketingType> find(string search)
        {
                DBLib dbl = new DBLib();

                String sxQuery =
                    @"select 
                    P_NPS_MARKETING_TYPE_ID,
	                TITLE,
	                DETAIL,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE,
	                VALID_FROM,
	                VALID_TO
                from 
                    P_NPS_MARKETING_TYPE
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    upper(Title) like upper('%" + search + @"%')
                ";
                DataTable dt = dbl.GetDataTable(sxQuery);

                List<Entities.NPSMarketingType> MarketingTypeList = new List<Entities.NPSMarketingType>();
                foreach (DataRow row in dt.Rows)
                {
                    Entities.NPSMarketingType MarketingType = new Entities.NPSMarketingType();
                    MarketingType.iID = Convert.ToInt16(row["P_NPS_MARKETING_TYPE_ID"].ToString());
                    MarketingType.sxTitle = row["TITLE"].ToString();
                    MarketingType.sxDetail = row["DETAIL"].ToString();
                    MarketingType.sxCreator = row["CREATE_BY"].ToString();
                    MarketingType.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                    MarketingType.sxUpdater = row["UPDATE_BY"].ToString();
                    MarketingType.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                    MarketingType.dtmValidFrom = DateTime.Parse(row["VALID_FROM"].ToString());
                    MarketingTypeList.Add(MarketingType);
                }
                return MarketingTypeList;
            
        }

        public Entities.NPSMarketingType getNPSMarketingType(int MarketingTypeID)
        {
            DBLib dbl = new DBLib();
            String sxQuery =
                @"select 
                    P_NPS_MARKETING_TYPE_ID,
	                TITLE,
	                DETAIL,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE,
VALID_FROM,
VALID_TO
                from 
                    P_NPS_MARKETING_TYPE
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    P_NPS_MARKETING_TYPE_ID = :prmID
                ";

            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = MarketingTypeID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            Entities.NPSMarketingType MarketingType = new Entities.NPSMarketingType();
            try
            {
                MarketingType.iID = Convert.ToInt16(dt.Rows[0]["P_NPS_MARKETING_TYPE_ID"].ToString());
                MarketingType.sxTitle = dt.Rows[0]["TITLE"].ToString();
                MarketingType.sxDetail = dt.Rows[0]["DETAIL"].ToString();
                MarketingType.sxCreator = dt.Rows[0]["CREATE_BY"].ToString();
                MarketingType.dtmCreated = DateTime.Parse(dt.Rows[0]["CREATE_DATE"].ToString());
                MarketingType.sxUpdater = dt.Rows[0]["UPDATE_BY"].ToString();
                MarketingType.dtmUpdated = DateTime.Parse(dt.Rows[0]["UPDATE_DATE"].ToString());
                MarketingType.dtmValidFrom = DateTime.Parse(dt.Rows[0]["VALID_FROM"].ToString());

            }
            catch (Exception p) { errLog = p.ToString(); }
            return MarketingType;
        }

    }
}